Introduction

SQL is a query and programming language that allows you to develop and administer databases and interactively manipulate, store or retrieve stored data from a relational database. SQL is an abbreviation for “Structured Query Language” and, according to the American National Standards Institute, is the standard language used in Relation Database Systems. It uses “statements” to perform certain tasks such as updating a database or retrieving data. Some of the more common ‘relational database management system’ users of SQL include: Microsoft, Oracle, MySQL, Sybase, Ingress, Postgres, MS Access, and Informix, to name a few.

-The “MS SQL Server” uses T-SQL

-The “MS Access version” of SQL can be called “JET SQL” (native format)

-Oracle uses PL/SQL

Although SQL is used in most database systems, many of them have additional unique proprietary extensions exclusive to their own system. However, standard commands on SQL such as: “Select”, “Update”, “Insert”, “Create”, “Drop”, and “Delete” can be used on any system to accomplish almost anything that needs to be done on a database. This e-book explains the conceptual underpinnings of relational database technology and is a guide to a problem solving approach that will assist when applying SQL techniques. It provides instructions on the basics of SQL commands and how to put them into practice, including database creation and how to fetch and modify rows or delete data.

A Short History of SQL

-In the year 1970, a researcher from IBM by the name of Dr. Edgar F. Codd formally defined “The Relational Model” from which most of the conceptual core of SQL is drawn and became known as “the father of relational databases”.

-In 1974 Donald Chamberlin started work on the “System/R Project” from which the SEQUEL (Structured English Query Language) was developed implemented on the IBM prototype “SEQUEL-XRM. During 1976 – 1977 it was rewritten entirely to include multi-user and multi-table features, and was re-named “SQL”.

-Methodical testing during 1978 to demonstrate the practicality and usefulness of the system resulted in success for IBM when they started to develop SQL implemented commercial products based on the System/R Prototype.

-In 1981 IBM introduced SQL/DS and in 1983, DB2.

Following these developments, other software vendors, recognizing the elevation of the relational model, started to develop their own SQL-based products, including ORACLE, who beat IBM by first marketing their commercial RDBMS inRDBMS in 1979. Other software vendors included Sybase and Ingres, which was based on the Berkeley Ingres Project from the University of California.

Why Use SQL?

SQL allows people using the system to do the following:

-Set specific permissions on views, procedures and tables

-Store procedure functions and create a view within a database

-Drop and create tables in databases

-Embed within languages with the use of pre-compilers, libraries and SQL modules

-Define data used in a database as well as being able to manipulate this data

-Describing data

-Accessing data within “relational database management systems”

TABLE BASICS

Relational database systems contain objects called “tables”, in which the information, or data, of the database are stored. Each table is uniquely identified by its name and comprises columns and rows.

COLUMNS contain information such as:

-Column name

-Data type

-… and any other column attributes

ROWS contain specific data or record pertaining to the columns.

Below is an example of a table named “Weather”. The Columns contain information such as “City”, “State”, “Highs”, and “Lows” pertaining to the weather. The Rows contain the specific data for this particular table of information:

Weather

City State Highs Lows

Tucson Arizona 105 92

Phoenix Arizona 101 90

San Diego California 77 60

Albuquerque New Mexico 80 72

Flagstaff Arizona 88 69

THE SQL PROCESS

When a user executes SQL commands used for any type of RDBMS, the SQL system will determine the ideal method in carrying out the request. The SQL engine figures out the best way of interpreting the task.

There are a number of components involved in the SQL process. Some of these include “SQL Query Engine”, “Classic Query Engine”, “Optimization Engines”, “Query Dispatcher” and more. The “Classic Query Engine” is designed to handle non-SQL queries,queries; however, the “SQL” query engine will not handle certain types of logical files.

SQL COMMANDS

There are a number of SQL commands that are used on a regular basis for SQL queries. Once these commands are understood, the user will know the basics on how to retrieve data from databases using SQL. The commands covered in this section include the following:

-“Select”

-“Distinct”

-“Where”

-“And Or”

-“In”

-“Between”

-“Wildcard”

-“Like”

-“Order by”

-“Group by”

-“Having”

-“Alias”

-“As”

-“Select Unique”

-“Insert Into”

-“Insert Into" “Select”

-“Update”

-“Delete From”

SELECT

The “Select” statement is used when a query is presented to the database to retrieve specifically selected data from one or more tables located in the database. There are two keywords present: the user will need to “SELECT” information “FROM” the table. The table in a database is the “container” where data is stored and is the most basic “SQL” query structure. The column name/s following the “select” keyword will determine which column information will be retrieved from the database and presented in the results. One or more column names can be selected, or “*” can be used to select all columns. The table name following the “from” keyword specifies the table to be queried in retrieving the required results.

Below is the format for a simple “select” statement:

SELECT the/ “column_name” /FROM the /“table_name”

The three methods used for retrieving data from tables include:

-Retrieve one of the columns

-Retrieve “multiple” columns

-Retrieve all of the columns

Below is an example of a table used to illustrate these three cases:

Store_Information: Table

Store_Name Sales Transaction_Date

Boston 1500 Jan-06-2000

Los Angeles 550 Jan-07-2000

San Diego 300 Jan-08-2000

Los Angeles 250 Jan-08-2000

To Select “One” Column

When selecting one column, the user must specify the name of the column between the “SELECT” and “FROM” in the following way:

“ SELECT the Store_Name FROM the Store_Information”

The result will be:

Store_Name

Boston

Los Angeles

San Diego

Los Angeles

To Select “Multiple” Columns

The “SELECT” statement can be used to retrieve multiple columns at a time. In order to select the Sales and Store_Name columns within the Store_Information, the following SQL is used:

“ SELECT“SELECT the "Store_Name", Sales FROM the Store_Information”

The result will be:

Store_Name Sales

Boston 1500

Los Angeles 550

San Diego 300

Los Angeles 250

To Select “All” the Columns

There isare two methods used for selecting all of the columns within a table. The first one involves listing the name of the column from each of the columns. The second method is far easier and uses the symbol *. In order to select every column from the Store_Information table, the SQL used includes the following:

“ SELECT * FROM the Store_Information”

The result will be:

Store_Name Sales Transaction_Date

Boston 1500 Jan-06-2000

Los Angeles 550 Jan-07-2000

San Diego 300 Jan-08-2000

Los Angeles 250 Jan-08-2000

DISTINCT

The SELECT keyword is used for retrieving information found in one or more columns in a table. The next keyword is DISTINCT used to select distinct elements. To accomplish this in SQL the user will need the keyword “DISTINCT” after the “SELECT” keyword. This syntax will appear as follows:

The “ SELECT“SELECT” “DISTINCT” from “column_name” and “FROM” the “table_name”

An example, selecting all the distinct stores within the Store_Information

Store_Information Table

Store_Name Sales Transaction_Date

Boston 1500 Jan-06-2000

Los Angeles 550 Jan-07-2000

San Diego 300 Jan-08-2000

Los Angeles 250 Jan-08-2000

The user will key in the following,

“SELECT DISTINCT" Store_Name FROM Store_Information

The result will be:

Store_Name

Boston

Los Angeles

San Diego

WHERE

The “where” clause is optional and will specify which rows, or data values, will be retrieved for display based on the selected criteria entered after the keyword “where”. There are conditional selections that can be used with the “where” clause:

= Equal

<> Not equal to

>= Greater than equal

<= Less than equal

< Less than

> Greater than

WHERE is used to filter a result based on specific conditions. When using “WHERE” in the “SELECT” statement, the following will occur:

(“SELECT” the column_name type in

“FROM” and table_name type in

“WHERE” condition)

The “CONDITION” can be used as a “single comparison” clause known as a “simple condition” or as a “multiple comparison” clauses which are combined together when using the “AND” or the “OR” operators known as a “compound condition.”

First example of store information: “WHERE” clause using a “simple condition”

To select the stores that have sales that is above $1,000 from the Store_Information Table

Store_Information Table

Store_Name Sales Transaction_Date

Boston 1500 Jan-06-2000

Los Angeles 550 Jan-07-2000

San Diego 300 Jan-08-2000

Los Angeles 250 Jan-08-2000

The user will key in the following:

(“SELECT” the Store_Name type in

“FROM” and Store_Information type in

“WHERE” and Sales>1000)

The result will be:

Store_Name

Boston

Second example of store information: “WHERE” clause using the “OR” operator:

View data that have sales that are more than $1,000 “or” with a transaction date: “Jan-08-2000”, this SQL will be used:

(“SELECT” *

“FROM” Store_Information

“WHERE” Sales>1000 “OR” Transaction_Date= “Jan-08-2000”)

The result will be:

Store_Name Sales Transaction_Date

Boston 1500 Jan-06-2000

San Diego 300 Jan-08-2000

Los Angeles 250 Jan-08-2000

Using “WHERE” with “UPDATE” and “DELETE”

With the “SELECT” statement, users can make use of the “WHERE” clause in conjunction with the “UPDATE” and the “DELETE” statements.

AND OR

In the last section, the user was able to view that the keyword “WHERE” is usable by conditionally selecting data from tables. A “condition” is either a type of “simple condition” or a “compound condition.” The “compound conditions” consist of multiple “simple conditions” that are either connected by “OR” or “AND.” There are no limitations to the amount of “simple conditions” that may be present for a “single” SQL statement.

Syntax for the use of a type of “compound condition” and what it will look like:

(“SELECT” the column_name type in

“FROM” and table_name type in

“WHERE” and simple condition

{ [AND/OR] simple condition} +)

The symbol {}+ will explain the expressions that feature within the stated brackets and can occur once or multiple times. The [AND/OR} will explain that “AND” or “OR” will be used. The parenthesis symbol {} is used for displaying the order relating to the condition.

For example:

SELECT stores that have sales over $1,000 or stores that have sales lower than $500, however larger than $275, found in the Store_Information Table.

Store_Information Table

Store_Name Sales Transaction_Date

Boston 1500 Jan-06-2000

Los Angeles 550 Jan-07-2000

San Diego 300 Jan-08-2000

Los Angeles 250 Jan-08-2000

The user will key in the following:

(“SELECT” the Store_Name type in

“FROM” the Store_Information type in

“WHERE” and Sales >1000 type in

“OR” and(Sales<500 AND Sales>275))

The result will be:

Store_Name

Boston

San Diego

IN

When using SQL, the “IN” keyword has two uses. This section involves the keyword associated with the “WHERE” clause. In this specific context the user will know the exact value on returned values on at least a single column. The “IN” keyword for this syntax will appear as:

(“SELECT” the column_name type in

“FROM” and table_name type in

“WHERE” and column name type in “IN” (value 1, value 2))

The amount of values present in the parentheses () can either be a single value or multiple values. Each of these values must be separated by the use of a comma. The values are numerical or the use of characters can be utilized. When using a single value within the parenthesis, the command will appear as follows:

“WHERE” column_name = “value 1”

An example: selecting all records associated with the Boston and Los Angeles stores present in the Store_Information:

Store_Information Table

Store_Name Sales Transaction_Date

Boston 1500 Jan-05-2000

Los Angeles 250 Jan-07-2000

San Diego 300 Jan-08-2000

San Fransico 700 Jan-08-2000

The user will key in the following:

(“SELECT” *

“FROM” Store_Information

“WHERE” Store_Name “IN” (Boston, Los Angeles))

The result will appear as follows:

Store_Name Sales Transaction_Date

Boston 1500 Jan-05-2000

Los Angeles 250 Jan-07-2000

BETWEEN

The IN keyword assists the user in limiting selection criteria for single or multiple discretionary values. The “BETWEEN” operator will be in use for selecting a range.

The “BETWEEN” syntax appears as follows:

(“SELECT” the column_name type in

“FROM” the table_name type in

“WHERE” column name and “BETWEEN” and value 1 “AND” value 2)

This syntax selects all the rows that have columns that consist of values that range between the “value 1”and the “value 2.”

An example:

Selecting to view sales information that ranges between January the 6, 2000 and January the 10, 2000, from the following table:

Store_Information Table

Store_Name Sales Transaction_Date

Boston 1500 Jan-05-2000

Los Angeles 250 Jan-07-2000

San Diego 300 Jan-08-2000

San Fransisco 700 Jan-08-2000

The user will key in the following:

(“SELECT” *

“FROM” Store_Information

“WHERE” Transaction_Date “BETWEEN” January-06-2000 “AND” January-10-2000)

The date can often be stored in a different type of format depending on the database.

The result will appear as follows:

Store_Name Sales Transaction_Date

Los Angeles 250 Jan-07-2000

San Diego 300 Jan-08-2000

San Fransisco 700 Jan-08-2000

The “BETWEEN” operator is inclusive, this means that the “value 1” and the “value 2” have been included in the final result. If the user would like to exclude these values, but include the information found in between, the query would need to be altered and would appear as follows:

(“ SELECT” the column_name type in

“ FROM” the table_name type in

“WHERE” the ( column_name> “value 1”)type in

“AND” the ( column_name< “value 2”))

Alternatively the user can make use of “BETWEEN” operator in order to exclude “values”. The operator “NOT” will be placed in front of the “BETWEEN” operator.

An example:

To display rows where the column for sales are not between the numbers 280-1000, the SQL used will appear as follows:

(“ SELECT” *

“ FROM” Store_Information

“WHERE” Sales “NOT BETWEEN” 280 - 1000)

The result will appear as follows:

Store_Name Sales Transaction_Date

Boston 1500 Jan-05-2000

Los Angeles 250 Jan-07-2000

WILDCARD

There will be times that a user may be searching for a method to latch onto what is known as a “string pattern”. This requires the wildcard concept. SQL has two types of wildcard functions:

-The percent sign (%) which represents one or multiple characters and the number zero

-The underscore sign ( _ ) which represents a single character

The wildcard operators are accompanied with the “LIKE” keyword when using SQL.

Here are a few examples with wildcards:

-“A_Z”: Any string that begins with the letter “A” followed by another character ending in “Z.”

An example:

“ATZ” and “A7Z” are strings that conform with this condition, while the string “AOOZ” does not as there are now two characters that fall between the “A” and the “Z”.

-“DEF%”: Any string that begins with “DEF.”

An example:

“DEFG” and “DEFDEF” are both strings that conform to this condition.

-“%KLM”: Any string that ends with “KLM”.

An example:

“JKLM” or “JJKLM” are strings that conform to this condition.

-“%AN%”: Any string that contains a pattern with “AN” anywhere.

An example:

“SAN DIEGO” and “LOS ANGELES” both conform to this condition.

-“_AN%”: Any string that contains a character, followed by “AN”, and then proceeded by something else.

An example:

“SAN DIEGO” conforms to this condition; however, “LOS ANGELES” does not conform.

LIKE

The keyword “LIKE” is another type of keyword used with the conditional selection of the “WHERE” clause. It is a powerful operator allowing the user to select only the rows that are “like” the specified criteria. In simple terms “LIKE” allows the user to conduct a search that has been based on patterns opposed to specifying the desired outcome such as the “IN” keyword, or for spelling out the range such as the in “BETWEEN.” The “%” (percentage sign) may be used as a “wild card” to match a possible character that may appear before or after specified characters.

The syntax used for this keyword will look like this:

(“SELECT” the column_name type in

“FROM” then table_name type in

“WHERE” the column_name and the“LIKE {PATTERN})

The {PATTERN} will often be made up of wildcards. Below is an example of how a wildcard can be used with the “LIKE” keyword:

Store_Information Table

Store_Name Sales Transaction_Date

Boston 1500 Jan-05-2000

Los Angeles 250 Jan-07-2000

San Diego 300 Jan-08-2000

San Francisco 700 Jan-08-2000

To find stores that contain “AN” in their names, the user will key in the following:

(“SELECT” *

“FROM” Store_Information

“WHERE” Store_Name “LIKE” %AN%)

The result will appear as follows:

Store_Name Sales Transaction_Date

Los Angeles 250 Jan-07-2000

San Diego 300 Jan-08-2000

San Francisco 700 Jan-08-2000

ORDER BY

So far the user has viewed how to obtain data from tables by using the commands known as “WHERE” and “SELECT”. However, often the output will need to be listed in a specific order. These orders include descending, ascending, or they can be based on text value or numerical values. For these instances the “ORDER BY” keyword is required.

The syntax used for the “ORDER BY” statement will appear as follows:

(“SELECT” the column_name then type in

“FROM” the table_name then type in

[“WHERE” condition]then type in

“ORDER BY” the column_name type in [ASC, DESC])

The symbol [] will defines that “WHERE” statements are optional. However, when there is a clause by the name of “WHERE” present, it will appear before an “ORDER BY” type of clause.

“ASC” defines that these results will appear in an ascending order, while DESC, defines that these results will appear in a descending order.

“ORDER BY” can be used for multiple columns. In these cases the “ORDER BY” clause will appear as follows:

“ORDER BY” the column_name1 which is “[ASC, DESC] from ” column_name2 “ for[ASC,DESC]]

An example:

Listing the contents of the Sales found in a Store_Information Table in descending order:

Store_Information Table

Store_Name Sales Transaction_Date

Boston 1500 Jan-05-2000

Los Angeles 250 Jan-07-2000

San Diego 300 Jan-08-2000

San Francisco 700 Jan-08-2000

The user will key in the following:

(“SELECT” Store_Name, “SALES”, Transaction_Date

“FROM” Store_Information

“ORDER BY” Sales DESC)

The result will appear as follows:

Store_Name Sales Transaction_Date

Boston 1500 Jan-05-2000

San Francisco 700 Jan-08-2000

San Diego 300 Jan-08-2000

Los Angeles 250 Jan-07-2000

Based on SQL query, the user can also utilize the column position in order to indicate the column to apply an “ORDER BY” clause to. The initial column will be “1, while the second column will be named “2“ and so forth. From the example above, the user can achieve the exact same results using this command:

(“SELECT” Store_Name, “SALES”, Transaction_Date

“FROM” Store_Information

“ORDER BY” 2 DESC)

The column or columns used for retrieving the result do not need to appear in the “SELECT” clause. An example of this would appear as follows:

(“SELECT” the Store_Name type in

“FROM” and Store_Information type in

“ORDER BY” Sales DESC)

The result will appear as follows:

Store_Name

Boston

San Francisco

San Diego

Los Angeles

In addition, it is possible to retrieve and sort the results by using an expression. An example using the table below:

Product_Sales Table

Product_Identification Price Units

1 8 9

2 12 4

3 18 3

The following is used for retrieving results according to Revenue which have been defined as “PRICE”* “UNITS”:

(“SELECT” Product_Identification, “PRICE”* “UNITS REVENUE”

“FROM” Product_Sales

“ORDER BY” “PRICE” * “UNITS” DESC)

The result will appear as follows:

Product_Identification Revenue

1 72

3 54

2 48

GROUP BY

The “GROUP BY” keyword is used for calculating totals of sales for every store present in a table. There are two requirements needed for this function that include selecting the names of the stores and the total sales. The second requirement is to ensure that the sales “figures” have been “grouped by” stores. The syntax used for this query includes the following:

(“SELECT” the “column_name1” from “SUM” of (column_name2)

“FROM” the ” table_name”

And “GROUP BY” the column_name1)

Below is an illustration using this table:

Store_Information Table

Store_Name Sales Transaction_Date

Boston 1500 Jan-06-2000

Los Angeles 550 Jan-07-2000

San Diego 300 Jan-08-2000

Los Angeles 250 Jan-08-2000

The goal is to retrieve the sales total for every store present. The user will key in the following:

(“SELECT” Store_Name, and “SUM”(Sales)type in

“FROM” and Store_Information type in

“GROUP BY” the Store_Name)

The results will appear as follows:

Store_Name SALES (SUM)

Boston 1500

Los Angeles 800

San Diego 300

“GROUP BY”

One of the most common uses of the “GROUP BY” function will be for time periods such as Month- Date-Week and even hour. These types of queries are often used with the “ORDER BY” keyword in order to retrieve a type of query result displaying time series.

An example:

To retrieve the total daily sales using Store_Information, the SQL that will be used will appear as follows:

(“SELECT” Transaction_Date, “SUM”(Sales)

“FROM” Store_Information

“GROUP BY” Transaction_Date)

The result will appear as follows:

Transaction_Date SUM(Sales)

Jan-06-2000 1500

Jan-07-2000 550

Jan-08-2000 550

HAVING

Another function users search for is to limit outputs according to a corresponding sum or other types of aggregate functions. An example would be when the user would like to view only sales that are in excess of $1,400.

As opposed to the use of the “WHERE” clause for the “SQL” statement, the “HAVING” clause is used for aggregate functions. When using the “HAVING” clause, it will generally be positioned close to the end of SQL statements. SQL statements that contain what is known as a “HAVING” clause can either include or not include the “GROUP BY” clause.

The syntax used with a “HAVING” clause will appear as follows:

(“SELECT” [column_name1], “FUNCTION” from [column_name2]

“FROM” the “table_name”

The “GROUP BY” and “column_name1”])

“HAVING”

The user can use zero for single or multiple columns with the “aggregate” function. When selecting zero columns, the “GROUP BY” clause is not required.

In the example using Store_Information Table:

Store_Information Table

Store_Name Sales Transaction_Date

Boston 1500 Jan-06-2000

Los Angeles 550 Jan-07-2000

San Diego 300 Jan-08-2000

Los Angeles 250 Jan-08-2000

The user will type in the following:

(“SELECT” Store_Name, “SUM”(Sales)

“FROM” Store_Information

“GROUP BY” Store_Name

“HAVING SUM”(Sales)>1400)

The result would appear as follows:

Store_Name SUM(Sales)

Boston 1500

ALIAS

The alias function consists of one of two types. These include “table alias “and “column alias”. The column aliases are used to assist with organizing the output. In the above example, when total sales appears it appears as “SUM” (Sales). This example will make more sense in other cases where the “column heading” becomes complicated. Using the column alias makes the output far easier to read and understand.

The other alias type is the “table alias”. This keyword is used by placing the alias after a “table name” when operating in the “FROM” clause. This function becomes convenient when the user is looking to derive information from two or more tables. This function is known as “perform joins.”

The syntax utilized for table and column aliases includes the following:

(“SELECT” the table_alias, from “column_name1”, column_alias

“FROM” and table_name and “table_alias”)

Both of these alias types are positioned after an item that they alias and that is separated by a space. Below is another example using the Store_Information Table

Store_Information Table

Store_Name Sales Transaction_Date

Boston 1500 Jan-06-2000

Los Angeles 550 Jan-07-2000

San Diego 300 Jan-08-2000

Los Angeles 250 Jan-08-2000

The very same example has been used that appeared in the SQL “GROUP BY”, except for the introduction of the table alias and column alias:

(“SELECT” the A1.Store_Name and Store,

type in“SUM”(A1.Sales) “Total Sales”

“FROM” the Store_Information” A1 and

type in “GROUP BY” A1.Store_Name)

The result appears as follows:

Store Total Sales

Boston 1500

Los Angeles 800

San Diego 300

AS

In the section about SQL Alias, the syntax utilized for column and table aliases appears as follows:

(“SELECT” the table_alias. from “column_name(1)” column_alias

“FROM” table_name “table_alias”)

The “AS” keyword is utilized for assigning aliases to a table or a column. This keyword is positioned between a column alias or column name or between a table alias and a table name. The syntax for “AS” appears as follows:

(“SELECT” the table_alias . and type in “column_name(1)” “AS” the column_alias

“FROM” the table_name and “AS” the table_alias)

Utilizing the “same examples” used in the above “SQL Alias” section. The following Store_Information Table appears as follows:

Store_Information Table

Store_Name Sales Transaction_Date

Boston 1500 Jan-06-2000

Los Angeles 550 Jan-07-2000

San Diego 300 Jan-08-2000

Los Angeles 250 Jan-08-2000

To derive the total sales for each store when using the “AS” keyword as a part of a column and table alias, the user will type in the following:

“SELECT” A1. then “Store_NameStore”, then

“SUM”(A1.Sales) then “AS” then Total Sales, then

“FROM” the Store_Information “AS”A1

“GROUP BY” the A1. and “Store_Name”

The result will appear as follows:

Store Total Sales

Boston 1500

Los Angeles 800

San Diego 300

INSERT INTO

When using SQL, in general, there are two methods to use in order to insert data into tables. The first involves inserting data in the process of a single row each time. The other method involves inserting multiple rows simultaneously.

The syntax used for the insertion of data into tables using a single row appears as follows:

“INSERT INTO” type in table_name then type in (column1, column2,…)and then

“VALUES” and (value1,value2,…)

Here is an example:

Store_Information Table

Name of Column Data Type

Store_Name char(50)

Transaction_Date Time/date

Sales Float

Manager_Identification Integer

Used to insert another row that will represent “sales data” for San Diego on the 10 January, 2000. On this day, the store did $500 sales, the Manager_Identification for the store is 8. The SQL script used will include the following:

(“INSERT INTO” Store_Information, (Store_Name, Transaction_Date, Sales, Manager_Identification)

“VALUES”(San Diego,8,500, “Jan-10-2000”))

The table will consist of the following:

Store_Information Table

Store_Name Transaction_Date Sales Manager_Ident

San Diego Jan-10-2000 500 8

The names of the columns can appear in any type of order. An example of this is that the “SQL” statement before will be equal to the “SQL” statement above:

“INSERT INTO” Store_Information (Management_Identification, Sales, Transaction_Date, Store_Name)

“VALUES” (8, 500, “Jan-10-2000”, San Diego)

When not specifying a “Column Name” In the “INSERT INTO” statement, the user will have to ensure that the data has been placed in the right column and in order from the table. An example:

(“INSERT INTO” Store_Information (Store_Name, Transaction_Date, Sales, Management_Identification)

“VALUES” (San Diego, “Jan-10-2000”, 500, 8))

When leaving off column names when using the “INSERT INTO” statement, the data must be placed in the same order that it appears in the table.

An example:

(“INSERT INTO” Store_Information

“VALUES” (San Diego, “Jan-10-2000”, 500,8))

Will result in the correct order, while:

(“INSERT INTO” Store_Information

“VALUES” ( 8, 500, “Jan-10-2000”, San Diego))

will result in the Store_Name set as 8, the date set as 500, the sales set as the date and the Manager_Identification set as San Diego.

INSERT INTO and SELECT

In the last section, users are taught how they can insert single values into the table, one single row at a time. The next step involves inserting more than one row into tables. This is achieved by using the “INSERT INTO” combined with a “SELECT” statement. The syntax for this example appears as follows:

(“INSERT INTO” type in table1 and then(column1, column2,…)

“SELECT” the column3, column4,… then type in

“FROM” table2)

This is a simple example. An entire statement will usually contain HAVING, GROUP BY, and WHERE clauses along with aliases and table joins.

Below is an example using these tables:

Store_Information Table

Column Name Data Type

Txn_Date Datetime

Store_Name char(50)

Sales Float

Sales_Data Table

Column Name Data Type

Sales Float

Store_Name char(50)

Transaction_Date Datetime

Product_ID Integer

The Sales_Data Table contains detailed information on sales, while the Store_Information Table contains data that is summarized for sales by day and by store. To move the data in the Sales_Data to the Store_Information, the user will type in the following:

(“INSERT INTO” Store_Information (Transaction_Date, Store_Name, Sales)

“SELECT” SUM(Sales), Store_Name, Transaction_Date

“FROM” Sales_Information

“GROUP BY” Store_Name, Transaction_Date

“UPDATE”)

Once there is data present in a table, the user may find it necessary to modify some of the data. In order to perform this function the “UPDATE” command is used. The syntax used for this command includes the following:

(“UPDATE” the table_name then type in

“SET” type in column_1 = type in “[new value]type in

“WHERE” condition)

An example:

Store_Information Table

Store_Name Sales Transaction_Date

Boston 1500 Jan-06-2000

Los Angeles 550 Jan-07-2000

San Diego 300 Jan-08-2000

Los Angeles 250 Jan-08-2000

The user notices that the sales for San Diego on Jan-08-2000 should be $600 instead of the amount $300. This entry will then need to undergo an update. The following “SQL” query will be used:

(“UPDATE” Store_Information

“SET Sales” = 600

“WHERE” Store_Name = “San Diego”

“AND” Transaction_Date = “Jan-08-2000”)

The result will appear as follows:

Store_Information Table

Store_Name Sales Transaction_Date

Boston 1500 Jan-06-2000

Los Angeles 550 Jan-07-2000

San Diego 600 Jan-08-2000

Los Angeles 250 Jan-08-2000

In this example, there is a single row which will satisfy a condition with a “WHERE” clause. Where there happens to be more than one row that satisfies the condition, all these rows are modified. When there is no “WHERE” clause, every row is modified.

UPDATE “MULTIPLE COLUMNS”

In addition, it is possible to perform a function for updating multiple columns simultaneously. The syntax used will appear as follows:

(“UPDATE” the table_name then type in

“SET” “column_1”=[value1], “column_2”=[value2]

“WHERE” condition)

An example:

If the user notices that the “Boston” entry on the table represents the wrong Transaction_Date and Sales_information. The process to correct the SQL statement is as follows:

(“UPDATE” Store_Information

“SET Sales” = 1000, Transaction_Date= “Jan-20-2000”

“WHERE” Store_Name= “Boston”)

The table will appear as follows:

Store_Information Table

Store_Name Sales Transaction_Date

Boston 1000 Jan-20-2000

Los Angeles 550 Jan-07-2000

San Diego 300 Jan-08-2000

Los Angeles 250 Jan-08-2000

When utilizing an “UPDATE” statement, the user should be careful to ensure that the condition has been specified.

DELETE FROM

In certain cases the user may use a query in order to remove specific records in a table. This requires the “DELETE FROM” command.

The syntax used for this command will appear as follows:

(“DELETE FROM” table_name

“WHERE” condition)

Below is an example using the “DELETE FROM” condition:

Store_Information Table

Store_Name Sales Transaction_Date

Boston 1500 Jan-06-2000

Los Angeles 550 Jan-07-2000

San Diego 300 Jan-08-2000

Los Angeles 250 Jan-08-2000

In this example, the user decides to delete any information relating to the Los Angeles store in the table. The SQL used for this example includes:

(“DELETE FROM” Store_Information

“WHERE” Store_Name= “Los Angeles”)

The table will appear as follows:

Store_Information Table

Store_Name Sales Transaction_Date

Boston 1500 Jan-06-2000

San Diego 300 Jan-08-2000

In the above example, criteria used in order to decide which rows should be deleted are considered simple, however, there are often more complex conditions.

The next example involves a complex condition with the use of a “subquery” as a condition. The example consists of two tables:

Store_Information Table

Store_Name Sales Transaction_Date

Boston 1500 Jan-06-2000

Los Angeles 550 Jan-07-2000

San Diego 300 Jan-08-2000

Los Angeles 250 Jan-08-2000

Table Geography

Region_Name Store_Name

West San Diego

West Los Angeles

East New York

East Boston

In order to delete or remove data relating to any stores that fall in the “East” region in the Store_Information, the SQL syntax used is as follows:

(“DELETE FROM” Store_Information

“WHERE” Store_Name “IN”

(“SELECT” Store_Name “FROM” Geography

“WHERE”Region_Name= “EAST”))

When this statement has been executed, the “Store_Information” will become

Store_Name Sales Transaction_Date

Los Angeles 550 Jan-07-2000

San Diego 300 Jan-08-2000

Los Angeles 250 Jan-08-2000

When the “WHERE” keyword used with the “DELETE FROM” command has been omitted, all the rows in a table will be deleted. In order to avoid this, it is advisable to practice in a database management system by running a corresponding “SELECT” command to ensure that all the rows that have been selected are the chosen ones the user intends to remove from a table. This action is possible when the user replaces the “DELETE” command with the “SELECT” * command.

It is of importance to note that a “DELETE FROM” command is unable to delete a row or more than one row of data when it violates certain types of constraints or a “FOREIGN KEY.”

10 SIMPLE STEPS TO UNDERSTANDING SQL

Many programmers tend to think that SQL is a difficult database language to grasp. SQL is described as a type of declarative language as it behaves in a very different manner when compared to functional, object-oriented or imperative languages.

The following information on SQL is designed for:

-Users who may have previously worked or tried to work with SQL. These users are people who may not have gained a full understanding of the language.

-Users who may already are quite familiar with SQL, but have failed to grasp its syntax.

-Users who may be interested in teaching SQL.

This information focuses on the “SELECT” statement and is broken down into 10 easy to understand steps.

1.SQL Described As Declarative

This is an important concept to understand. SQL allows users to declare the results they are searching for and not how the computer computes the results. An example to explain this includes the following:

(“SELECT” first_name, surname “FROM” employees “WHERE” salary > 100000)

This is a simple example that is easily understandable. The purpose of the statement is to locate employees that are earning a salary of more than 150,000 each month.

Important Lesson for the User

This example seems simple, however, the issue is that many people think along the lines of “imperative programming”. This often includes calling functions, iterating, writing loops, storing results temporarily in variables and more.

When it comes to SQL, it is advisable to forget about imperative programming. The user will need to rather focus on “how to declare” things and to avoid thinking about how to instruct the machine on computing things.

2. Syntax Used for SQL Described as not “Well-Ordered”

The common source for confusion around the “SQL” syntax elements relates to the fact they will not be ordered in the same way that they become executed. Lexical ordering includes the following:

-ORDER BY

-UNION

-HAVING

-GROUP BY

-WHERE

-FROM

-SELECT [DISTINCT]

Not every SQL clause has been listed. The lexical ordering is extremely different from logical orders, which includes the “order of execution”:

-ORDER BY

-UNION

-DISTINCT

-SELECT

-HAVING

-GROUP BY

-WHERE

-FROM

There are three important things the user must remember:

1)The initial clause is “FROM” and not “SELECT”. SQL works by first loading data that is derived from a disk into the memory, so that it is able to operate on the data.

2)The “SELECT” clause will be executed after the majority of the other clauses. The two most important clauses that “SELECT” comes after will include “GROUP BY and “FROM”. This concept is important for users to understand when trying to reference items declared from the “SELECT” clause that come from the “WHERE” clause. Below is an example that displays a mistake:

(“SELECT” A.b + A.c “AS” t

“FROM” A

“WHERE” t =10 ( t is not available here))

If the user would like to reuse t, there are 2 options available. One would be to repeat this expression as follows:

(“SELECT” A.b + A.c “AS” t

“FROM” A

“WHERE” (A.b + A.c) = 10)

The other method would be to use a common type of table expression, derived tables, or views in order to avoid code repetition.

3)The “UNION” clause will always be positioned before the “ORDER BY” clause for logical and lexical ordering. Many users are under the impression that the “UNION” sub select can be used before the “ORDER BY” clause. However, according to many SQL dialects and SQL standards, this is not the case. While a few dialects may allow for derived tables or ordering sub queries, there are possibilities that this type of ordering may not be retained once a “UNION” operation has been implemented.

Not all the databases will implement certain things in the same manner. For example: the second rule mentioned, will not apply in the same way to SQLite, PostgresSQL and MySQL.

Important Lesson for the User

Remembering the logical order and lexical order relating to SQL clauses in order to avoid making common mistakes. When this distinction is understood it will become very obvious why certain clauses work and others may not.

3. SQL Is Related To Table References

Due to the differences present in logical ordering and lexical ordering, many beginners become tricked in thinking that the column values are important in SQL. This is not true, as the more important part of SQL is the table references.

According to the “SQL” standard, the “FROM” clause is defined as follows:

(“ : := FROM

[ { }….]”)

The “output” from the “FROM” clause is described as a combined “table reference” for the “combined degree” relating to all “table references.”

FROM c, d

The above statement produces a type of combined “table reference” which includes the “degree” of c+ the “degree” of d. If c consists of 4 columns and d consists of 6 columns, the “output table” results in 10 ( 4 + 6) columns.

Records that are contained within this combined “table reference” form part of a cartesian product/cross product of “ c × d. This means that every record relating to c will be paired with every record relating to d. This means that if c has 4 records and d has 6 records, the combined “table reference” produces 24 records (4 × 6).

This “output” is then “piped” or “fed” into a “GROUP BY” clause, whereby it transforms into another “output.”

When looking at these concepts from a set theory or relational algebra perspective, SQL tables are sets of tuples or a relation. In addition, every SQL clause transforms one or more relations to produce new types of relations.

Important Lesson for the User

To think in the terms of “table references” in order to gain an understanding of how the data will be “pipelined” through the use of SQL clauses.

4.SQL “Table References” Are Powerful

Table references in relation to SQL are considered to be powerful. One example of this power would be the “JOIN” keyword. This keyword is not a part of “SELECT” statement, but actually is part of the “special” table reference. A joined table, defined by SQL standards is as follows:

(“

: :=

|

| ”)

Using the example from above:

FROM c, d

c can appear as a joined table in the following manner:

c1 JOIN c2 ON c1.id = c2.id

When expanding this from the last expression, the user would get:

FROM c1 JOIN c2 ON c1.id = c2.id, d

The result of the combined “table reference” will appear with a degree that includes c1 + c2 + b. The derived tables are considered to be more powerful when compared to joined tables.

Important Lesson for the User

To always think in the terms of “table references.” Just as important as it is to understand the process of how data is fed through SQL clauses, it also important to understand how the complex “table references” have been constructed.

In addition, it is important to understand that the “JOIN” keyword is used for constructing the joined tables and does not form part of the “SELECT” statement. Certain databases will allow the “JOIN” keyword to be used in DELETE, UPDATE and INSERT.

5.Use of SQL “JOIN” Tables Are Preferable to Comma-separated Tables

Using the above example:

FROM c, d

Any advanced type of SQL developer will agree that it is not advisable to make use of comma-separated lists. Instead it is encouraged to rather express the JOINs. This assists in improving readability of SQL statements and prevents mistakes.

One common mistake often made is related to forgetting a “JOIN” predicate somewhere. An example:

(“FROM” i, j, k, l, m, n, o, p

“WHERE” i.i1 =j.jx

“AND” i.i2 =k.k1

“AND” l.l1 =j.jk)

Using a “join table” syntax is :

-Much safer, as the user is able to position join predicates that are close to joined tables. This prevents mistakes from occurring.

-The “join table” is more expressive and it is easier to distinguish between INNER JOIN, OUTER JOIN, etc.

Important Lesson for the User

To always try to use “JOIN”. Avoid using comma-separated “table references” in the “FROM” clauses.

6. Various JOIN Operations in SQL

The JOIN operations are available in five ways:

-DIVISION

-CROSS JOIN

-ANTI JOIN

-SEMI JOIN

-EQUI JOIN

These types of terms are often used for relational algebra. SQL uses these terms in a different manner.

DIVISION

In summary, if you compare JOIN to multiplication, then DIVISION is explained as an inverse of “JOIN”.

CROSS JOIN

This “JOIN” produces what is known as a “cross product” for two joined “table references”. It combines all records in the first “table reference” with all records from the second “table reference”. This can also be achieved when using comma-separated “table references” from the “FROM” clause. In very rare cases, the user can use a “CROSS JOIN” for most of the SQL dialects.

ANTI JOIN

This type of relational concept can be described as an opposite of the SEMI JOIN. This JOIN is produced by adding the “NOT” keyword to EXISTS or IN predicates

The same rules apply in accordance with readability, expressivity and performance.

SEMI JOIN

This type of relational concept is expressed in one of two ways for SQL. Using an EXISTS predicate or IN predicate. The word “SEMI” is translated into “half” in Latin. This join type is used for joining “half” of “table references.” Using a previous example involving the joining of book and author. The next example involves eliminating the need for book and author combinations and rather focusing on the authors that have books. The example will appear as follows:

(-- Using “IN”

“FROM” the author

“WHERE” the author,id “IN” (SELECT book.author_id “FROM” book)

--Using the “EXISTS”

“FROM” the author

“WHERE EXISTS” and (SELECT 1 “FROM” book “WHERE” book.author_id = author.id)

There are no specific rules pertaining to whether the user should prefer EXISTS or IN, however, the following is important to note:

-The “IN” predicates are generally easier to read compared to the “EXISTS” predicates

-The “EXISTS” predicates are generally more expressive compared to the “IN” predicates. - There may be no formal differences in their performances. There are, however large performance differences in certain databases.

As the “INNER JOIN” can also produce just the authors who have books, various beginners tend to think they are able to remove duplicates when using the “DISTINCT”. An example of this is expressing a “SEMI JOIN” as follows:

(-- “ Locate only the authors who already have books”

then type in “ SELECT DISTINCT” and “first_name”, and “last_name”

then type in“FROM” author

and “JOIN” the book “ON” with (author.id that is = book./author_id))

The following is not good practice for two important reasons:

-It becomes extremely slow; the database will need to load excess data into the memory in order to remove duplicates.

-The statement is not completely correct, even though it may produce the right result. However, when a user begins to “JOIN” up more “table references”, it becomes a hard task to remove the duplicates from the end result.

EQUI JOIN

The EQUI JOIN is considered to be the most popular and common “JOIN” operation. It is used in two ways that include:

-OUTER JOIN which is further broken down to “FULL OUTER JOIN”, RIGHT and LEFT

-INNER JOIN or can be known as just JOIN.

Important Lesson for the User

SQL is all about “table references”. The “joined tables” are types of sophisticated table references. However, there is a distinction between SQL-speak and relational-speak. Not every “relational” join operation will be a “formal SQL” join operation. With knowledge and practice about the relational theory, any user can select the correct relational “JOIN” type and translate the statement to the right SQL.

7.Derived Tables For SQL Are Similar To Table Variables

Previously, users learned that the SQL language is a type of “declarative language”, which meant that variables have no place. However, variables definitely have a place in certain SQL dialects. These variables in SQL are known as derived tables.

These derived tables are actually just subqueries wrapped up in parentheses.

(-- “A derived table”

“From” ( SELECT * FROM author))

It is important to note that certain SQL dialects may require that the derived tables must have a “correlation name” which also goes by the name of “alias.”

“ – A derived table with an alias”

“FROM” ( SELECT * FROM author) a

The derived tables are good when a user would like to circumvent any problems that can be caused from “logical ordering” of the SQL clauses.

For example: if the user would like to reuse or repeat a “column expression” in the “WHERE” and “SELECT” clause, all that is needed is to type in (Oracle dialect)

(“ - - Obtain author’s first_names and last_names, and their ages in days”

“SELECT” “first_name”, “last_name”, “age”

“FROM” ( “SELECT” first_names, last_names, age, date of birth-current_date “FROM” author)

-- When age is more than 15000 days

“WHERE” age > 15000)

It is important to note that certain databases, along with the “SQL 1999 standard”, have now taken the derived tables onto a new level with the introduction of the “common table” expressions. This introduction now allows users to reuse a derived table multiple times in one “SQL SELECT” statement.

(“ WITH” a “AS” ( SELECT age, date of birth-current_date, last_name, first_names “FROM” author)

“SELECT” *

“FROM” a

“WHERE” age > 15000)

The “a” can further undergo externalization, into a type of “standalone view” for broader use of the common type of SQL subselects.

Important Lesson for the User

As mentioned previously, SQL is more about the “table references” and not about the columns.

8.The SQL “GROUP BY” Clause Transforms Previous “Table References”

Going back to the previous “FROM” clause:

“FROM” c, d

And now apply the “GROUP BY” clause to the combined “table reference”:

“GROUP BY” C.x, C.y, D.z

This will produce a new “table reference” that will feature just three columns. When applying the “GROUP BY” clause, the amount of the available columns will be reduced. This will apply to every subsequent logical clause, including the “SELECT” clause. This is a syntactical reason as to why users are only able to reference one or more columns from a “GROUP BY” clause in a “SELECT” clause. However, other columns can still be open for aggregate function:

(“SELECT C.x, C.y, “SUM”(C.z)

“FROM” C

“GROUP BY” C.x, C.y)

It is of importance to note that the database known as MySQL, does not adhere to this specific standard, which can cause a bit of confusion. The “GROUP BY” clause transforms the “table references” This means that the user can only reference the columns that have been referenced in “GROUP BY” clauses.

Important Lesson for the User

The “GROUP BY” clause operates with “table references” and transforms these into new forms.

9.“SQL SELECT” is Known as “Projection” according to “Relational Algebra”

Once a table reference has been generated, transformed and filtered, the next step involves projecting this table reference into another form. Using the “SELECT” clause is similar to a projector. The “table function” makes use of what is known as a “row value expression” in order to transform every record that comes from a previously constructed “table reference” for the final product.

When using the “SELECT” clause, the user can now operate on the columns to create complex types of column expressions that form part of rows or records.

There are various special rules that pertain to the specific nature of the available functions and expressions. The ones that are of greater importance include the following:

-Column references can only be used when they are produced by an “output” table reference.

-When there is a “GROUP BY” clause present, the user can only reference the columns from that specific clause or from the aggregate functions.

-The use of window functions is allowed instead of the aggregate functions ,if the “GROUP BY” clause is absent.

-When there is no “GROUP BY” clause, it is not permitted to combine non-aggregate functions with functions.

-There are specific rules in place about wrapping aggregate functions with regular functions.

In fact there are a number of involved and complex rules that are related to SQL. One example of this is the reason why users cannot combine non-aggregate functions with functions for the projection relating to “SELECT” statements that do not have a “GROUP BY” clause present. This is because:

-It will intuitively not make any sense

-There are syntax rules in place. The SQL:1999 standard introduced the GROUPING SETS, while the SQL:2003 standard introduced what is known as “empty” grouping sets: The “GROUP BY” (). When there is an “aggregate function” present and a “GROUP BY” clause is absent, then an empty, implicit “GROUPING SET” will be applied according to rule 2. This means that most of the older original rules relating to “logical ordering” are not true anymore. This also means that PROJECTION (SELECT) will influence outcomes of lexically succeeding and logically preceding clauses for “GROUP BY.”

Important Lesson for the User

In the previous section it becomes known that the “SELECT” clause is considered to be a very complex clause in relation to SQL. All the other types of clauses “pipe” the “table references” from one to the next. The “SELECT” clause can have an effect on the other “table references”, by transforming them and the application of specific rules.

In order for users to fully understand and master SQL, it is import to have an understanding of everything else relating to SQL before taking on the “SELECT” clause. Even though the “SELECT” clause is the initial clause used in the lexical ordering, it must be taken on last.

10.SQL OFFSET, ORDER BY, UNION and DISTINCT

-Ordering operations include FETCH, OFFSET and ORDER BY

-Set operations include UNION and DISTINCT

Ordering Operations

Ordering has not been viewed as a type of relational feature. In fact it is actually only an SQL feature. This feature is applied at the end of the logical ordering and lexical ordering of the SQL statement. Using FETCH, OFFSET and ORDER BY are the only methods to guarantee that the records are accessed in a safe and reliable way in index format. All types of other ordering will be random and arbitrary even when they seem to be fairly reproducible.

The OFFSET…FETCH has only one type of syntax variant present. The other variants are: Sybase’s “TOP…START AT”; SQL Server’s; PostgresSQL’s; “LIMIT… OFFSET”; and MySQL’s.

Set Operations

The set operations are known to operate on the “sets”. These are actually just tables and are fairly easy to understand.

-INTERSECT will retain only the records that are contained and present in both of the subselects. It also removes duplicates.

-EXCEPT will remove records that exist in the initial subselect, this includes records in the second “ subselect” and then will remove any duplicates.

-UNION ALL will concatenate two different subselects while retaining duplicates.

-UNION will concatenate two different subselects and then proceed onto removing any duplicates.

-DISTINCT is a function that will remove any duplicates after a projection.

Mostly, removing of duplicates does not make sense. In most cases it is advisable to use the “UNION ALL” , when looking for a method to concatenate the subselects.

As with most software languages, SQL will require a lot of practice in order to master the skill. The 10 steps mentioned above are designed to assist users to make sense of SQL. In addition, when learning about SQL it is advisable to make mistakes and learn from them in order to speed up the learning process.

SQL FUNDAMENTALS

The SQL commands are divided by two types of main sublanguages. This includes DDL ( Data Definition Language) which is comprised of commands that destroy and create database objects and databases. After a database structure has been defined using DDL, the database administrators, along with users, are now able to use the DML ( Data Manipulation Language). This language is for modifying and retrieving data that is contained in the table.

DDL (Data Definition Language)

DDL has been created in order to destroy and create database objects and databases. The commands are generally utilized by administrators at either a setup or removal phase of database projects. Below are the usage and structure of basic types of DDL commands:

Create

Installation of a DBMS ( Database Management System) on computers allows the user to manage and create various independent databases.

An example:

The user may be looking to maintain databases for customer contacts that may be required for the sales department as well as personnel databases for the HR department. Use of the “CREATE” command is essential for establishing every type of database associated with the company’s platform. An example of this would appear as follows:

Use of the command:

“CREATE DATABASE” personnel

This command will create a database that is empty with the name “personnel” on the DBMS. The next step involves creating a table for containing data. The other variant of a “CREATE” command is usable for this particular purpose:

The use of the command:

“CREATE TABLE” personal_information[first_names characters(20) not null, employee_identification int not null, last_name characters(20) not null,]

This query will create a table with the title “personal_info” in the existing database. In the above example this table contains the following attributes: the “first_names”, “employee_identification”, “last_name.”

USE

This command allows the user to specify which database they would like to be working on within the DBMS. An example of this would be if a user is currently working with an employee database and they wish to issue a command that will have an effect on a sales database. The following command should be used:

USE sales

It is of importance that the user keeps in mind at all times the specific database they happen to be working with, before any SQL commands are issued that can manipulate data.

ALTER

Once a table has been created in a database, the user may decide to modify its definition. An “ALTER” command allows the user to make the required changes to a structure of the table without recreating and deleting it. Below is an example using the “ALTER” command:

“ALTER TABLE” personal_information

“ADD” monthly_income money null

This particular example will add in a new attribute into the table named personal_info – an employee’s monthly income. The “money” attribute is used to specify that the employee’s income is stored in a dollars and a cents type of format. The “null” keyword is used to instruct the database in use that it is ok for the field to have “no value” for any of the employee’s in the database.

DROP

The last command used in DDL is “DROP”, which allows the user to remove whole objects from the DBMS database. An example of this would be if the user wishes to remove the sales table permanently. The following command can be used:

“DROP TABLE” sales

Another example of the command can be used to remove and permanently eliminate the entire personal_info:

“DROP DATABASE” personal_info

It is highly important that users are very cautious when working with this particular command as it is has the ability to remove complete structures that are present in the database. For removing any individual records, it is advisable to rather use the “DELETE” command used in the “Data Manipulation Language.”

The above information is a short summary of the DDL (Data Definition Language). The next part involves information on how DML (Data Manipulation Language) can be used for manipulating information that is present inside existing databases.

DML (Data Manipulation Language)

DML is used for modifying, inserting and retrieving database information. The commands present in the DML will be utilized by every type of database user in the normal routine operations of databases. Below is a summarized look at basic types of DML commands:

INSERT

In SQL the “INSERT” command has the function of adding in records to any of the existing tables. In the previous section an example of personal_info data was used. The next example involves the creation of data where the HR department in a company will need to add an employee that is new to the company into the existing database that contains all other employees. The following command can be used:

“INSERT INTO” personal_information

“values” ( “John”, 45675, “Smith”, $35000)

In this example there are 4 values that have been specified. These values correspond to the attributes in the table according to the order in which they have been defined, which includes:

first_names, employee_identification, last_name and monthly_income.

SELECT

In SQL the most commonly used command will be the “SELECT” command. This command allows for database users to obtain specific information from operational databases. Below are some examples that use the “personal_information from the employee’s database.

The command that is displayed below is used for retrieving all the information that is present in the table named personal_information. The asterisk * that appears in the example is known as a type of “wildcard” often used in SQL. The asterisk gives the database the command to “Select everything present within the personal_information table”:

(“SELECT” *

“FROM” personal_information)

In some cases, the database user will want a limitation set on the attributes obtained in the database. An example of this would be: the HR department in a company needs a list that contains only the surnames of each of the employee’s currently working for the company. Below is the SQL command needed for the retrieval of this specific information:

(“SELECT” last_name

“FROM” personal_information)

“WHERE” Clause

The “WHERE” clause is used in SQL for limiting the records that are obtained with those that meet up with the specified criteria. For example: A CEO of a company may have an interest in reviewing personnel records that only include employees that receive high paying monthly incomes. Below is the command used for the retrieval of any data in the personal_information of records that contain monthly income values that are greater than $20,000:

(“SELECT” *

“FROM” personal_information

“WHERE” salary> $20000)

UPDATE

This command is used by database users for modifying the information that exists in a table, individually or in bulk. An example of this a company that provides their employee’s each year with an increase of 4% for cost-of-living to their existing monthly-income. Below is the SQL command used that will apply this for every one of the employee’s that have been stored within this particular database:

“UPDATE” personal_information

“SET salary” = “salary” *1.04

Another example involves a new employee by the name of John Smith that has managed to demonstrate performance that is exceptional. The management of the company wants to recognize the outstanding accomplishments of the employee, by providing him with a raise of $2,000. This is where the “WHERE” clause can be used for providing John Smith with a raise to his monthly_income. The query will appear as follows:

(“UPDATE” personal_information

“SET salary” = salary + $2000

“WHERE” employee_identification = 45675)

DELETE

The syntax for the “DELETE” command is very similar to many of the DML commands. An example of the this command involves that the company’s latest report on corporate earnings showed results that were unable to meet the expectations required. Unfortunately, John Smith loses his job due to this unforeseen loss. For this example the “WHERE” clause and “DELETE” command are used for removing the employee from the existing personal_information table:

(“DELETE FROM” personal_information

“WHERE” employee_identification = 45675)

JOIN Statements

Now that the basics relating to SQL have been covered, database users can begin to use a powerful concept offered by SQL known as the “JOIN” statement. “JOIN” statements allow users the ability to combine specific data in more than one table to efficiently and quickly process larger amounts of data simultaneously. These types of statements are where the power of the databases resides.

The basic use of a JOIN operation is for combining specific data that appears in two or more tables. The more advanced use of the JOIN operation includes inner and outer joins for

 

---